"""
FIVEDASH(TM) -- General Purpose Accounting Software
Copyright (C) 2008  Obol Software Pty Ltd

This program is free software: you can redistribute it and/or modify
it. For terms and conditions, see fivedash/license/license.txt

"""

from datetime import datetime
import logging

from mod_python import apache
from mod_python import Session
from mod_python import util

from phunc import *
import phunc.cx_wrapper.db_tools as db_tools
from phunc.logger import logger

# legacy namespacing
unquote = utils.unquote
get_formatted_address = utils.get_formatted_address
contact_log,lcontact_log = table.contact_log, table.lcontact_log
save_upload_file = utils.save_upload_file
get_whole_save_path = utils.get_whole_save_path
get_whole_file_name = utils.get_whole_file_name
get_file_name_by_id = utils.get_file_name_by_id
genErrorPage, showErr = error.genErrorPage, error.showErr
GeneralBusinessErr = exception.GeneralBusinessErr
address=block.address
place_of_business_choice = table.place_of_business_choice

def index( req,**params ):
	"""displays a list of contacts by company"""
	page = Page(req, company_db=True)
	page_params = {}
	broadcast_message = params.has_key('broadcast_message') and params['broadcast_message'] or ''
	page_num = params.has_key('page') and params['page'] and int(params['page']) or 1
	sql_str = "SELECT company_name, given_name, surname, phone, TRIM(COALESCE(email, ' ')) AS email, company_id, person_id, iscustomer, issupplier FROM contact_info"
	entity_name=None
	if params.has_key("entity_name") and params['entity_name'].strip():
		entity_name = params['entity_name']
		if entity_name:
			if "WHERE" in sql_str:
				sql_str = sql_str+" AND (lower(company_name) LIKE '%%%s%%' OR lower(given_name) || ' ' || lower(surname) LIKE '%%%s%%')"%(entity_name.strip().lower(),entity_name.strip().lower())
			else:
				sql_str = sql_str+" WHERE lower(company_name) LIKE '%%%s%%' OR lower(given_name) || ' ' || lower(surname) LIKE '%%%s%%'"%(entity_name.strip().lower(),entity_name.strip().lower())
			page_params['entity_name'] = entity_name
	searchkey = ''
	if params.has_key('searchkey') and params['searchkey']:
		searchkey = params['searchkey']
		if searchkey.lower()!='all':
			if "WHERE" in sql_str:
				sql_str = sql_str+" AND lower(company_name) LIKE '%s%%' OR (company_name IS NULL AND lower(given_name) || ' ' || lower(surname) LIKE '%s%%')"%(searchkey.lower(),searchkey.lower())
			else:
				sql_str = sql_str+" WHERE lower(company_name) LIKE '%s%%' OR (company_name IS NULL AND lower(given_name) || ' ' || lower(surname) LIKE '%s%%')"%(searchkey.lower(),searchkey.lower())
			page_params['searchkey'] = searchkey
	page.content = {
		'no_render':table.contacts_by_company(page.company_db.cursor(), info.query_string,page.session['skin'],sql_str,page_num,10,"/bin/crm.py",page_params)
	}
	page.setup = {
		'breadcrumbs':['CRM'],
		'menu_title':'CRM Portal',
		'menu':['newcontact'],
		'content_title':'Search',
		'broadcast_message':broadcast_message,
		'template':'no_render.opt'
	}
	page.info = {
		'query_string':info.query_string,
	}
	return page.render()

def get_company_info( req,**params ):
	"""displays a page of company information, with edit links, etc"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	company_info = db_tools.exec_action(dbc,'get_customer_company_info',[params['id']])
	person_list = db_tools.exec_action(dbc,'get_person_id_list',[params['id']])
	page.content = {
		'id':params['id'],
		'office_list':[],
		'lcontact_log':lcontact_log(dbc,person_list,int(params['id']),page.session['skin']),
		'iscustomer':False
	}
	company_name = ''
	if len(company_info) > 0:
		page.content['iscustomer'] = True
		customer_id = company_info[0]['id']
		company_name = company_info[0]['name']
		page.content['custmer_id'] = customer_id
	else:
		company_info = db_tools.exec_action(dbc,'get_company_info2',[params['id']])
		company_name = len(company_info)>0 and company_info[0]['name'] or ""
	broadcast_message = None
	pob_info_all=db_tools.exec_action(dbc,'get_pop_info1',[params['id']])
	rowcount = len(pob_info_all)
	for pob_info in pob_info_all:
		if company_name == '':
			company_name = pob_info['name']
		address = ''
		if pob_info['address_id'] != None:
			office_address = db_tools.exec_action(dbc,'get_address_info',[pob_info['address_id']])[0]
			address = get_formatted_address(office_address,return_one_line=True)
		contact_info = {
			'name':pob_info['name'] != None and  pob_info['name']  or "",
			'phone':pob_info['phone'] != None and  pob_info['phone']  or "",
			'fax':pob_info['fax'] != None and  pob_info['fax']  or "",
			'address':address
		}
		page.content['office_list'].append( contact_info )		
	list_setup={
		'classid':{'main':'title title2','detail':'table'},
		'template_dir':info.getTemplateDir(req),
		'isfo':page.content['office_list'],
		'title1':['Address','Phone','Fax'],
		'key':['address','phone','fax'],
		'main':'name',
	}
	ulist = form.user_div(**list_setup)
	page.content['ulist']=ulist
	page.setup = {
		'breadcrumbs':['crm',company_name],
		'menu_title':'CRM Portal',
		'menu':['newcontact'],
		'content_title':company_name,
		'broadcast_message':broadcast_message,
		'template':'crm/sc.opt'
	}
	page.info = {
		'query_string':info.query_string,
		'name':company_name
	}
	return page.render()

def get_person_log( req,**params ):
	"""displays a form for editing an action item or contact log item"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	p_id = params['pid']
	c_id = params['cid']
	person_name=db_tools.exec_action(dbc,'get_person_name2',[p_id])[0]['person_name']
	if params.has_key('close') and params['close']!='':
		logid = params['close']
		log_entry=db_tools.exec_action(dbc,'get_log_entry',[logid])[0]
		personid = log_entry['entity_id']
		comment = log_entry['comment']
		filename = log_entry['filename']
		if filename != '':
			filepath = get_whole_file_name(get_whole_save_path(personid, logid), get_file_name_by_id(logid, filename))
		else:
			filepath = ''
		action = log_entry['action']
		page_title = 'Close Action'
		submit_label = 'Close Action / Save Changes'
	elif params.has_key('edit') and params['edit']!='':
		logid = params['edit']
		log_entry=db_tools.exec_action(dbc,'get_log_entry',[logid])[0]
		personid = log_entry['entity_id']
		comment = log_entry['comment']
		filename = log_entry['filename']
		if filename != '':
			filepath =get_whole_file_name(get_whole_save_path(personid, logid),get_file_name_by_id(logid, filename))[13:]
		else:
			filepath = ''
		action = log_entry['action']
		page_title = 'Edit Contact Log / Action'
		submit_label = 'Save Changes'
	else:
		logid = ''
		comment = ''
		filename = ''
		filepath = ''
		action = ''
		page_title = 'New Contact Log Entry'
		submit_label = 'Create'		  
	page.content = {
		'logid':logid,
		'comment':comment,
		'filename':filename,
		'filepath':filepath,
		'action':action,
		'submit_label':submit_label,
		'params_name':params.keys(),
		'params_list':params
	}
	page.setup = {
		'breadcrumbs':['crm','person',page_title],
		'menu_title':'CRM Portal',
		'menu':['newcontact'],
		'content_title':page_title,
		'template':'crm/sle.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string,
		'query_params':{'pid':p_id,'cid':c_id},
		'name':person_name
	}
	return page.render()

def create_contact( req,**params ):
	"""displays form 1 of 3 for creating a new contact - basic info"""
	page = Page(req)
	page.setup = {
		'breadcrumbs':['crm','New Contact'],
		'menu_title':'CRM Portal',
		'menu':['newcontact!'],
		'content_title':'New Contact',
		'template':'crm/scc1.opt',
		'form':True
	}
	page.info = {
			'query_string':info.query_string,
	}
	return page.render()

def create_contact_step2( req,**params ):
	"""displays form 2 of 3 for entering a new contact - location match chooser/company chooser & creator"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	matches = db_tools.exec_action(dbc,'get_place_of_business_info1',[params['phone'][:-3]])
	if len(matches) == 0 and params.has_key('fax') and params['fax']!='':
		matches=db_tools.exec_action(dbc,'get_place_of_business_info2',[params['fax'][:-3]])
	companies = db_tools.exec_action(dbc,'get_companies')
	entity_types = db_tools.exec_action(dbc,'get_c_entity_types')		
	page.content = {
		'matches':matches,
		'companies':companies,
		'entity_types':entity_types,
		'params_name':params.keys(),
		'params_list':params
	}
	if len(matches) > 0:
		match_list=[]
		for match in matches:
			match_dict={}
			address_bits_1 = [match['line1'],match['line2'],match['suburb'],match['region']]
			if address_bits_1.count(None) > 0:
				address_bits_1.remove(None)
			match_dict['id']=match['id']
			match_dict['address_bits_1']=address_bits_1
			match_list.append(match_dict)
		content['match_list']=match_list			
	page.setup = {
		'breadcrumbs':['crm','New Contact'],
		'menu_title':'CRM Portal',
		'menu':['newcontact!'],
		'content_title':'New Contact',
		'template':'crm/scc2.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def create_contact_step3( req,**params ):
	"""display form 3 of 3 for creating a new contact - work location"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	if params.has_key('place_of_business_id') and params['place_of_business_id']:
		var_map={'p_company_id':'null','p_company_name':'','entity_type':'','p_line1':'','p_line2':'','p_suburb':'','p_region':'','p_code':'','p_country':'','p_location_name':'','loc_phone':'','loc_fax':'','p_location_id':'null','p_surname':'','p_given_name':'','p_position':'','p_phone':'','p_fax':'','p_email':'','p_mobile':'','p_sms':''}
		var_map['p_location_id'] = params['place_of_business_id']
		pob_details = db_tools.exec_action(dbc,'get_pop_details',[params['place_of_business_id']])[0]
		other_possible_keys = ['given_name', 'surname', 'position', 'phone', 'fax', 'email', 'mobile', 'sms']
		# if work phone or fax is an exact match, don't store it
		if params['phone'] == pob_details['phone']:
			other_possible_keys.remove('phone')
		if params.has_key('fax') and params['fax'] == pob_details['fax']:
			other_possible_keys.remove('fax')
		for key in other_possible_keys:
			if params.has_key(key) and params[key]!='':
				var_map['p_'+key] = params[key]			
		result = db_tools.exec_action(dbc,'create_contact',conn,[var_map['p_surname'],var_map['p_given_name'],var_map['p_company_name'],var_map['p_position'],var_map['p_line1'],var_map['p_line2'],var_map['p_suburb'],var_map['p_region'],var_map['p_code'],var_map['p_country'],var_map['p_location_id'],var_map['loc_phone'],var_map['loc_fax'],var_map['p_phone'],var_map['p_fax'],var_map['p_email'],var_map['p_mobile'],var_map['p_sms'],var_map['p_company_id'],var_map['p_location_name'],var_map['entity_type']])[0]['create_contact']
		util.redirect(req,"/bin/crm.py/index?broadcast_message=%s"%str(result))
	page.content = {
		'address':address('validate-all-or-none'),
		'is_place_of_business_id':False,
		'is_company_id':False,
		'is_name':False,
		'params_name':params.keys(),
		'params_list':params
	}
	if params.has_key('company_id') and params['company_id']:
		page.content['is_company_id']=True
		work_location_choices = table.place_of_business_choice(dbc, params['company_id'], False)
		page.content['work_location_choices']=work_location_choices
	if params.has_key('name') and params['name']!='':
		page.content['is_name']=True
	broadcast = None
	page_title = 'New Contact'
	page.setup = {
		'breadcrumbs':['crm','New Contact'],
		'menu_title':'CRM Portal',
		'menu':['newcontact!'],
		'content_title'	:page_title,
		'broadcast_message'	:broadcast,
		'template':'crm/scc3.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string
	}
	return page.render()

def create_contact_complet( req,**params ):
	"""actually creates a new contact -- redirects to crm index page (contacts by company)"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()		
		var_map={'p_company_id':'null','p_company_name':'','entity_type':'','p_line1':'','p_line2':'','p_suburb':'','p_region':'','p_code':'','p_country':'','p_location_name':'','loc_phone':'','loc_fax':'','p_location_id':'null','p_surname':'','p_given_name':'','p_position':'','p_phone':'','p_fax':'','p_email':'','p_mobile':'','p_sms':''}
		pob_id = None
		need_alt_location = False
		if params.has_key('company_id') and params['company_id']!='':
			company_id = params['company_id']
			var_map['p_company_id'] = params['company_id']
		elif params.has_key('name') and params['name']!='':
			var_map['p_company_name'] = params['name']
			var_map['entity_type'] = params['entity_type']
		if params.has_key('line1') and params['line1']!='':
			if params.has_key('line2') and params['line2']!='':
				var_map['p_line2'] = params['line2'] 
			var_map['p_line1'] = params['line1']
			var_map['p_suburb'] = params['suburb'].lower()
			var_map['p_region'] = params['region'].lower()
			var_map['p_code'] = params['code'].lower()
			var_map['p_country'] = params['country'].lower()							   
			if params.has_key('pob_name') and params['pob_name']!='':				
				var_map['p_location_name'] = params['pob_name']		
			if params.has_key('pob_phone') and params['pob_phone']!='':		  
				var_map['loc_phone'] = params['pob_phone']
			if params.has_key('pob_fax') and params['pob_fax']!='':	 
				var_map['loc_fax'] = params['pob_fax']
		elif params.has_key('place_of_business_id') and params['place_of_business_id']!='':
			pob_id = params['place_of_business_id'] 
			var_map['p_location_id'] = pob_id
		other_possible_keys = ['given_name', 'surname', 'position', 'phone', 'fax', 'email', 'mobile', 'sms']
		if pob_id != None:
			pob_details = db_tools.exec_action(dbc,'get_pop_details2',[str(pob_id)])[0]
			if params['phone'] == pob_details['phone']:
				other_possible_keys.remove('phone')
			if params.has_key('fax') and params['fax'] == pob_details['fax']:
				other_possible_keys.remove('fax')	 
		for key in other_possible_keys:
			if params.has_key(key) and params[key]!='':
				var_map['p_'+key] = params[key]
		result = db_tools.exec_action(dbc,'create_contact',[var_map['p_surname'],var_map['p_given_name'],var_map['p_company_name'],var_map['p_position'],var_map['p_line1'],var_map['p_line2'],var_map['p_suburb'],var_map['p_region'],var_map['p_code'],var_map['p_country'],var_map['p_location_id'],var_map['loc_phone'],var_map['loc_fax'],var_map['p_phone'],var_map['p_fax'],var_map['p_email'],var_map['p_mobile'],var_map['p_sms'],var_map['p_company_id'],var_map['p_location_name'],var_map['entity_type']])[0]['create_contact']
		logger.debug(result)
		conn.commit()
		broadcast_message = showErr(int(result))
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/crm.py/index?broadcast_message=%s"%broadcast_message)

def get_alt_location( req,**params ):
	"""displays a form for creating an alt location (ie, for a contact)"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	broadcast_message = ''
	p_id=params['pid']
	c_id=params['cid']
	alt_location_types = []
	alt_location_types1=db_tools.exec_action(dbc,'get_alt_location_types') 
	exist_alt_location_types = db_tools.exec_action(dbc,'get_exist_alt_location_types',[p_id])
	if len(alt_location_types1)>0:
		if len(exist_alt_location_types)>0:  
			for alt_location_type in alt_location_types1:
				flag=True
				for exist_alt_location_type in exist_alt_location_types:
					if exist_alt_location_type['name']==alt_location_type['code_value']:
							flag=False
							break
				if flag==True:
					alt_location_types.append(alt_location_type)
		else:
			alt_location_types=alt_location_types1
	if len(alt_location_types1)>0 and c_id.upper()!='NONE':
		for alt_location_type in alt_location_types:
			if alt_location_type['code_value']=='work':
				alt_location_types.remove(alt_location_type)
				break
	person = db_tools.exec_action(dbc,'get_entity_by_id',[p_id])[0]
	person_name =' '.join([person['given_name'], person['name']])
	page.content = {
		'alt_location_types':alt_location_types,
		'p_id':p_id,
		'c_id':c_id
	}
	page.setup = {
		'breadcrumbs':['crm','person','Add Location'],
		'menu_title':'CRM Portal',
		'menu':['newcontact'],
		'content_title':'Add Location',
		'broadcast_message':broadcast_message,
		'template':'crm/sca.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string,
		'query_params':{'pid':p_id,'cid':c_id},
		'name':person_name
	}
	return page.render()

def get_alt_location_submit( req,**params ):
	"""actually creates an alt location for a contact -- redirects to person detail page"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()		
		broadcast_message = ''
		c_id = params['cid']
		p_id = params['pid']
		passthrough_fields = ['cid', 'pid']
		field_names = ''
		new_values = ''
		for field_name in params.keys():
			if field_name[:4] != 'old_' and passthrough_fields.count(field_name) == 0 and params[field_name] != '':
				field_names = field_names.strip('~:~') + '~:~' + field_name
				new_values = new_values.strip('~:~') + '~:~' + params[field_name]
		dbc.execute("SELECT edit_contact(%s,'','', string_to_array('%s', '~:~'), string_to_array('%s', '~:~'), 'NOW')" % (p_id, field_names, new_values))
		result = dbc.fetchone()[0]
		conn.commit()
		broadcast_message = showErr(int(result))
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/crm.py/get_contact_detail?cid=%s&amp;pid=%s&amp;broadcast_message=%s"%(c_id,p_id,broadcast_message))

def edit_contact2( req,**params ):
	"""displays form 1 of 3 for editing contact details"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	broadcast_message = ''
	p_id=params['pid']	   
	c_id = params['cid']		
	page.content = {
		'p_id':p_id,
		'c_id':c_id,
		'original_page_load':datetime.now()
	}
	contact_info = {}
	if c_id.upper()=='NONE' or not c_id.strip():
		contact_info = db_tools.exec_action(dbc,'get_contact_info',[p_id,'is NULL'])[0]
	else:
		contact_info = db_tools.exec_action(dbc,'get_contact_info',[p_id,'='+c_id])[0]
	page.content['is_address'] = False
	page.content['is_location'] = False
	pob_id = None
	address = None
	work_address = None
	pob_info = None
	if c_id:
		if c_id.upper() == 'NONE':
			pob_address = db_tools.exec_action(dbc,'get_work_details',[p_id])
			if pob_address:
				pob_id = pob_address[0]['address_id']
				work_address=db_tools.exec_action(dbc,'get_address_info',[str(pob_id)])[0]
				page.content['is_address'] = True
				page.content['pob_address'] = pob_address
				page.content['work_address'] = work_address
		else:
			##relationship = db_tools.exec_action(dbc,'get_relation_ship',[p_id,c_id])[0]
			pob_id = db_tools.exec_action(dbc,'get_pob_id',[p_id,c_id])
			if pob_id and pob_id[0]['location_id'] != None:
				pob_info=db_tools.exec_action(dbc,'get_pop_info2',[str(pob_id[0]['location_id'])])[0]
			if pob_info:				   
				if contact_info['phone'] == pob_info['phone']:
					contact_info['phone'] = None
				if contact_info['fax'] == pob_info['fax']:
					contact_info['fax'] = None
				pob_info['phone']=max(pob_info['phone'], '').replace(' ', '')
				address = pob_info
				page.content['is_location'] = True
				address = '<br />'.join([pob_info['name'], get_formatted_address(pob_info)]) 
	page.content['contact_info']=contact_info 
	logger.debug(contact_info)
	page.content['count_none']=contact_info.values().count(None)
	page.content['pob_info']=pob_info
	person_name = ' '.join([contact_info['given_name'], contact_info['surname']])
	if address == None and work_address != None:
		address = work_address
	page.content['address']=address
	page.content['person_name'] = person_name
	if pob_info != None or address != None or contact_info.values().count(None) < 7:
		if contact_info['email']:
			email_link = '<a href="mailto:' + contact_info['email'] + '">' + contact_info['email'] + '</a>'
		else:
			email_link = ''
		page.content['email_link']=email_link			  
	alt_locations = db_tools.exec_action(dbc,'get_alt_locations',[p_id])
	page.content['is_alt_locations']=False
	if len(alt_locations) > 0:	
		page.content['is_alt_locations']=True
		page.content['alt_locations']=alt_locations		
		page.content['alt_locations']=alt_locations
	page.setup = {
		'breadcrumbs':['crm','person','Edit'],
		'menu_title':'CRM Portal',
		'menu':['newcontact'],
		'content_title':'Edit Contact - ' + person_name,
		'broadcast_message':broadcast_message,
		'template':'crm/sce4.opt',
		'form':True,
		'javascript':True
	}
	page.info = {
		'query_string':info.query_string,
		'query_params':{'pid':p_id,'cid':c_id},
		'name':person_name
	}
	return page.render()

def edit_contact2_submit( req,**params ):
	"""actually updates contact details -- redirects to crm index page (contacts by company)"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()		
		broadcast_message = ''
		if params.has_key('cid'):
			c_id = params['cid']
		else:
			c_id = None
		p_id = params['pid']  
		person_name = params['person_name']
		surname = params['surname']
		given_name = params['given_name']
		passthrough_fields = ['given_name','surname','cid', 'pid', 'person_name', 'original_page_load']
		field_names = ''
		new_values = ''
		old_values = ''
		for field_name in params.keys():
			if field_name[:4] != 'old_' and passthrough_fields.count(field_name) == 0 and params[field_name] != '':
				field_names = field_names.strip('~:~') + '~:~' + field_name
				new_values = new_values.strip('~:~') + '~:~' + params[field_name]
		dbc.execute("SELECT edit_contact(%s,'%s','%s',string_to_array('%s', '~:~'), string_to_array('%s', '~:~'), '%s')" % (p_id,surname,given_name, field_names, new_values, params['original_page_load']))
		result = dbc.fetchone()[0]
		conn.commit()
		broadcast_message = showErr(int(result))
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/crm.py/index?cid=%s&amp;pid=%s&amp;broadcast_message=%s"%(c_id,p_id,broadcast_message))

def edit_contact2_step2( req,**params ):
	"""displays form 2 of 3 for editing contact details"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	broadcast_message = ''
	p_id = params['pid']
	c_id = params['cid']
	person_name = params['pname']
	page.content = {
		'p_id':p_id,
		'c_id':c_id,
		'person_name':person_name,
		'original_page_load':params['opl'],
		'companies':db_tools.exec_action(dbc,'get_companies'),
		'entity_types':db_tools.exec_action(dbc,'get_c_entity_types')
	}
	dbc.execute("SELECT detail FROM system.contact_detail WHERE entity_id = '%s' AND type = 'phone'" % p_id)
	phone = dbc.fetchone()
	dbc.execute("SELECT detail FROM system.contact_detail WHERE entity_id = '%s' AND type = 'fax'" % p_id)
	fax = dbc.fetchone()
	matches = []
	if c_id and c_id.upper()!='NONE':
		matches = db_tools.exec_action(dbc,'get_place_of_business_info3',[c_id])
	if len(matches) == 0 and phone != None:
		matches = db_tools.exec_action(dbc,'get_place_of_business_info1',[phone[0][:-3]])
	if len(matches) == 0 and fax != None:
		matches = db_tools.exec_action(dbc,'get_place_of_business_info2',[fax[0][:-3]])
	page.content['matches']=matches
	if len(matches) > 0:
		match_list=[]
		for match in matches:
			match_dict={}
			address_bits_1 = [match['line1'],match['line2'],match['suburb'],match['region']]
			if address_bits_1.count(None) > 0:
				address_bits_1.remove(None)
			match_dict['id']=match['id']
			match_dict['address_bits_1']=address_bits_1
			match_list.append(match_dict)
		page.content['match_list']=match_list
	page.setup = {
		'breadcrumbs':['crm','person','Edit'],
		'menu_title':'CRM Portal',
		'menu':['newcontact'],
		'content_title':'Edit Contact - ' + person_name,
		'broadcast_message':broadcast_message,
		'template':'crm/sce5.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string,
		'query_params':{'pid':p_id,'cid':c_id},
		'name':person_name
	}
	return page.render()

def edit_contact2_step3( req,**params ):
	"""displays form 3 of 3 for editing contact details"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	p_id = params['pid']
	c_id = params['cid']		
	person_name = params['person_name']
	if params.has_key('location_id') and params['location_id']:
		dbc.execute("SELECT edit_contact(%s,'','','{location_id}', '{%s}', '%s')" % (p_id, params['location_id'], params['original_page_load']))
		result = dbc.fetchone()[0]
		page.company_db.commit()
		broadcast_message = showErr(result)
		util.redirect(req,"/bin/crm.py/index?broadcast_message=%s"%broadcast_message)
	page.content = {
		'is_company_id':False,
		'is_name':False,
		'address':block.address('validate-all-or-none'),
		'params_name':params.keys(),
		'params_list':params
	}
	if params.has_key('company_id') and params['company_id']:
		page.content['is_company_id']=True
		work_location_choices = table.place_of_business_choice(dbc, params['company_id'], False)
		page.content['work_location_choices']=work_location_choices
	if params.has_key('company_name') and params['company_name']!='':
		page.content['is_name']=True
	broadcast_message = None
	page.setup = {
		'breadcrumbs':['crm','person','Edit Location'],
		'menu_title':'CRM Portal',
		'menu':['newcontact'],
		'content_title':'Edit Location - ' + person_name,
		'broadcast_message':broadcast_message,
		'template':'crm/sce6.opt',
		'form':True
	}
	page.info = {
		'query_string':info.query_string,
		'query_params':{'pid':p_id,'cid':c_id},
		'name':person_name
	}
	return page.render()

def edit_contact2_complet( req,**params ):
	"""actually updates contact details -- redirects to crm index page (contacts by company)"""
	try:
		session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()		
		broadcast_message = ''
		if params.has_key('cid'):
			c_id = params['cid']
		else:
			c_id = None
		p_id = params['pid']  
		person_name = params['person_name']
		passthrough_fields = ['cid', 'pid', 'person_name', 'original_page_load']
		field_names = ''
		new_values = ''
		old_values = ''
		for field_name in params.keys():
			if field_name[:4] != 'old_' and passthrough_fields.count(field_name) == 0 and params[field_name] != '':
				field_names = field_names.strip('~:~') + '~:~' + field_name
				new_values = new_values.strip('~:~') + '~:~' + params[field_name]
		dbc.execute("SELECT edit_contact(%s,'','', string_to_array('%s', '~:~'), string_to_array('%s', '~:~'), '%s')" % (p_id, field_names, new_values, params['original_page_load']))
		result = dbc.fetchone()[0]
		conn.commit()
		broadcast_message = showErr(result)
	except GeneralBusinessErr,e:
		broadcast_message = e.getErrMsg()
	except Exception,e: 
		errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
		broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
	except:
		broadcast_message = 'An undefined error occurred'
	finally:
		util.redirect(req,"/bin/crm.py/index?cid=%s&amp;pid=%s&amp;broadcast_message=%s"%(c_id,p_id,broadcast_message))

def get_contact_detail(req,**params):	
	"""displays details (including contact log) for a single contact"""
	page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
	broadcast_message = '' 
	if params.has_key('broadcast_message') and params['broadcast_message']:
		broadcast_message = params['broadcast_message']
	p_id=int(params['pid'])
	if params.has_key('cid')  and params['cid'].strip() and params['cid'].upper() != 'NONE':
		c_id=int(params['cid'])
	else:
		c_id=None
	page.content = {
		'p_id':p_id,
		'c_id':c_id,
		'is_customer':False
	}
	customer_id=db_tools.exec_action(dbc,'get_customer_id2',[p_id])
	if len(customer_id) > 0:
		page.content['is_customer']=True
		customer_id = customer_id[0]['id']
		page.content['customer_id']=customer_id
	#get comments and escapte
	if params.has_key('comment') and params['comment'] != '' and params['comment'] != None:
		unquote_comment = params['comment']
	else:
		unquote_comment = ''
	#get file name/value and escapte		
	if params.has_key('file') and params['file']!='' and params['file'].filename != '' and params['file'].filename != None:
		filename = params['file'].filename
		filevalue = params['file'].file.read()
		unquote_filename = filename
	else:
		filename = ''
		filevalue = ''
		unquote_filename = filename
	#get action and escapte
	if params.has_key('action') and params['action'] != '' and params['action'] != None:
		unquote_action = params['action']
	else:
		unquote_action = ''
	
	#operate accroding to diffrent submit value
	if params.has_key('submit') and params['submit'] != None:
		if params['submit'] == 'Create':
			contact_log_id = db_tools.exec_action(dbc,'get_contact_log_id')[0]['num']
			db_tools.exec_action(dbc,'insert_contact_log',[int(contact_log_id), int(p_id), unquote_comment, unquote_filename, unquote_action], conn=page.company_db)
			
			save_upload_file(get_whole_save_path(p_id, contact_log_id),get_whole_file_name(get_whole_save_path(p_id, contact_log_id), get_file_name_by_id(contact_log_id, filename)),filevalue)
			page.company_db.commit()
			broadcast_message = 'Entry added'	
		elif params['submit'] == 'Close Action / Save Changes':
			if filename == '':
				db_tools.exec_action(dbc,'update_contact_log1',[unquote_comment, unquote_action, params['logid']])
			else:
				db_tools.exec_action(dbc,'update_contact_log2',[unquote_comment, unquote_filename, unquote_action, params['logid']])
				save_upload_file(get_whole_save_path(p_id, params['logid']),get_whole_file_name(get_whole_save_path(p_id, params['logid']), get_file_name_by_id(params['logid'], filename)),filevalue)
			page.company_db.commit()
			broadcast_message = 'Action closed'
		elif params['submit'] == 'Save Changes':
			if filename == '':
				db_tools.exec_action(dbc,'update_contact_log3',[unquote_comment, unquote_action, params['logid']])
			else:
				db_tools.exec_action(dbc,'update_contact_log4',[unquote_comment, unquote_filename, unquote_action, params['logid'] ])					   
				save_upload_file(get_whole_save_path(p_id, params['logid']),get_whole_file_name(get_whole_save_path(p_id, params['logid']), get_file_name_by_id(params['logid'], filename)),filevalue)
			page.company_db.commit()
			broadcast_message = 'Entry updated'		
	contact_info = {}
	if c_id!=None:
		contact_info = db_tools.exec_action(dbc,'get_contact_info',[p_id,'='+str(c_id)])[0]
	else:
		contact_info = db_tools.exec_action(dbc,'get_contact_info',[p_id,'is NULL'])[0]
	pob_info = None
	address = None
	work_address = None
	pob_id = None
	page.content['is_address'] = False
	page.content['is_location'] = False
	if c_id!=None:
		pob_id = db_tools.exec_action(dbc,'get_pob_id',[p_id,str(c_id)])[0]['location_id']
		if pob_id:
			pob_info=db_tools.exec_action(dbc,'get_pop_info2',[str(pob_id)])[0]
		if pob_info:
			if contact_info['phone'] == pob_info['phone']:
				contact_info['phone'] = None
			if contact_info['fax'] == pob_info['fax']:
				contact_info['fax'] = None
			pob_info['phone']=max(pob_info['phone'], '').replace(' ', '')
			address = pob_info	 
			page.content['is_location'] = True
			address = ','.join([pob_info['name'], get_formatted_address(pob_info,return_one_line=True)])
	else:
		pob_id = db_tools.exec_action(dbc,'get_work_details',[p_id])
		
		if pob_id:
			work_address=db_tools.exec_action(dbc,'get_address_info',[str(pob_id[0]['address_id'])])[0]			
			page.content['work_address'] = work_address	
			page.content['is_address'] = True
	contact_info['phone']=max(contact_info['phone'], '').replace(' ', '')
	#---------------------contact_info------------------------#
	page.content['contact_info']=contact_info
	page.content['count_none']=contact_info.values().count(None)
	page.content['pob_info']=pob_info
	#------------------------------------------------------------------------------#
	person_name = ' '.join([contact_info['given_name'], contact_info['surname']])
	if address == None and work_address != None:
		address = get_formatted_address(work_address,return_one_line=True)
	#----------address-------------------#
	page.content['address']=address
	#-----------------------------------------#
	if contact_info['email']:
		email_link = '<a href="mailto:' + contact_info['email'] + '">' + contact_info['email'] + '</a>'
	else:
		email_link = ''
	#-----------email_link---------------#
	page.content['email_link']=email_link
	#-----------------------------------------#
	alt_locations = db_tools.exec_action(dbc,'get_alt_locations',[p_id])
	#----------------------is_alt_locations Boolean------#
	page.content['is_alt_locations']=False
	#----------------------------------------------------------------#
	if len(alt_locations) > 0:	
	##	alt_locations = dictfetch(dbc)
	#----------------------is_alt_locations Boolean------#
		page.content['is_alt_locations']=True			
	#----------------------------------------------------------------#
		address_list=[]
		for alt_location in alt_locations:
			alt_location['phone']=max(alt_location['phone'], '').replace(' ', '')
			address_dict={}
			address_dict['id']=alt_location['id']
			address_dict['address']=get_formatted_address(alt_location,return_one_line=True)  
			if alt_location['type'].find('address') > 0:
				address_dict['address_label']=alt_location['type']
			else:
				address_dict['address_label']='Address'
			address_list.append(address_dict)
	#---------------address_list,alt_location---------------------#
		page.content['alt_locations']=alt_locations
		page.content['address_list']=address_list
	#-----------------------------------------------------------------------#
	#---------------contact_log----------------------#
	page.content['contact_log']=contact_log(dbc, p_id, str(c_id), info.query_string,page.session['skin'])
	page.content['query_string'] = info.query_string.replace('?', '&amp;')
	#--------------------------------------------------------------------------#
	page.setup = {
		'breadcrumbs':['crm',person_name],
		'menu_title':'CRM Portal',
		'menu':['newcontact'],
		'content_title':person_name,
		'broadcast_message':broadcast_message,
		'template':'crm/sp.opt'
		}
	page.info = {
		'query_string':info.query_string,
		'name':person_name
	}
	return page.render()
        
def delete_contact(req, **params):
        try:
                session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()		        
                person_id = params['pid']
                dbc.execute('select delete_contact(%s)'%person_id)
                result = dbc.fetchone()[0]
                conn.commit()
                broadcast_message = error.showErr(result)
        except GeneralBusinessErr,e:
                broadcast_message = e.getErrMsg()
        except Exception,e: 
                errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
                broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
        except:
                broadcast_message = 'An undefined error occurred'
        finally:
                redirect_to = '/bin/crm.py/index?broadcast_message=%s'%broadcast_message
                if params.has_key("entity_name") and params['entity_name'].strip():
                        redirect_to = redirect_to + '&amp;entity_name='+params['entity_name'].strip()
                if params.has_key("searchkey") and params['searchkey'].strip():
                        redirect_to = redirect_to + '&amp;searchkey=' + params['searchkey'].strip()
                util.redirect(req,redirect_to)
                
def merge_contact_step1(req,  **params):
        page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
        pid = params['pid']
        persons = db_tools.exec_action(dbc,  'get_persons1', [pid])
        page.content = {
                'persons': persons, 
                'person1_id': pid
        }
        page.setup = {
                'breadcrumbs':['crm','Merge Contact'],
                'menu_title':'CRM Portal',
                'menu':['newcontact'],
                'content_title':'Merge Contact',
                'template':'crm/mc1.opt'
        }
        page.info = {
                'query_string':info.query_string
        }
        return page.render()
        
def merge_contact_step2(req,  **params):
        page = Page(req, company_db=True)
	dbc = page.company_db.cursor()
        from datetime import datetime
        person1 = db_tools.exec_action(dbc,  'get_persons2', [params['person1_id']])[0]
        person2 = db_tools.exec_action(dbc,  'get_persons2', [params['person2_id']])[0]
        page.content = {
                'person1': person1, 
                'person2': person2, 
                'original_page_load': datetime.now()
        }
        page.setup = {
                'breadcrumbs':['crm','Merge Contact'],
                'menu_title':'CRM Portal',
                'menu':['newcontact'],
                'content_title':'Merge Contact',
                'template':'crm/mc2.opt'
        }
        page.info = {
                'query_string':info.query_string
        }
        return page.render()
        
def merge_contact_step3(req,  **params):
        page = Page(req,  company_db=True)
        dbc = page.company_db.cursor()
        
        person1_id = params['k_person_id']
        person2_id = person1_id == params['person1_id'] and params['person2_id']  or params['person1_id']
        person1 = db_tools.exec_action(dbc,  'get_persons2', [person1_id])[0]
        person2 = db_tools.exec_action(dbc,  'get_persons2',  [person2_id])[0]
        
        location1_id = None
        if person1['company_id']:
                location1_id = db_tools.exec_action(dbc,  'get_location_id',  [person1_id,  person1['company_id']])[0]['location_id']
                person1_locations = db_tools.exec_action(dbc,  'get_person_locations2',  [person1_id,  location1_id,  person1_id,  person1_id])
        else:
                person1_locations = db_tools.exec_action(dbc,  'get_person_locations1',  [person1_id,  person1_id])
                 
        location2_id = None
        if person2['company_id']:
                location2_id = db_tools.exec_action(dbc,  'get_location_id',  [person2_id,  person2['company_id']])[0]['location_id']
                append_sql = ''
                if location1_id:
                        append_sql = 'and id <> '+ str(location1_id)
                person2_locations = db_tools.exec_action(dbc, 'get_person_locations3',  [person2_id,  location2_id,  append_sql,  person2_id,  person2_id])
        else:
                person2_locations = db_tools.exec_action(dbc, 'get_person_locations1',  [person2_id,  person2_id])
        
        for location1 in person1_locations:
                location1['checked'] ='Y'
                if not location1_id and location2_id and location1['name'] == 'work':
                        location1['checked'] = 'N'
                        
        for location2 in person2_locations:
                location2['checked'] = 'N'
                if not location1_id and location2_id and location2['combination'].split('_')[0] == 'c':
                        location2['checked'] = 'Y'
                elif location2['name'] != 'work' and location2['combination'].split('_')[0] != 'c':
                        flag = True
                        for location1 in person1_locations:
                                if location1['name'] == location2['name']:
                                        flag = False
                        if flag:
                                location2['checked'] = 'Y'
                
                                
        locations = person1_locations + person2_locations
        locations.sort()
        
        for location in locations:
                address = db_tools.exec_action(dbc,  'get_address_info',  [location['address_id']])[0]
                location['format_address'] = utils.get_formatted_address(address,  return_one_line = True)
        
        del(params['k_person_id'])
        page.content = {
                'locations': locations, 
                'params': params
                }
                
        page.setup = {
                'breadcrumbs':['crm','Merge Contact'],
                'menu_title':'CRM Portal',
                'menu':['newcontact'],
                'content_title':'Merge Contact',
                'template':'crm/mc3.opt',
        }
        page.info = {
                'query_string':info.query_string
        }
        return page.render()
        
def merge_contact_submit(req,  **params):
        try:
                session = Session.Session(req)
		check_access(req,session, req.uri)
		conn = get_connection(session)
		dbc = conn.cursor()	
                person1_id = params['person1_id']
                person2_id = params['person2_id']
                passthrough_fields = ['person1_id', 'person2_id', 'original_page_load']
                field_names = ''
                new_values = ''
                locations = ''
                for field_name in params.keys():
                        if  passthrough_fields.count(field_name) == 0 and field_name[0:2] != 'c_' and field_name[0:2] != 'p_':
                                field_names = field_names.strip('~:~') + '~:~' + field_name
                                if params[field_name]:
                                        new_values = new_values.strip('~:~') + '~:~' + params[field_name]
                                else:
                                        new_values = new_values + '~:~' + ' '
                        elif passthrough_fields.count(field_name) == 0:
                                locations = locations.strip('~:~') + '~:~' + params[field_name]
                
                dbc.execute("SELECT merge_contact(%s,%s, string_to_array('%s', '~:~'), string_to_array('%s', '~:~'),string_to_array('%s', '~:~'), '%s')"%(person1_id,  person2_id,  field_names, new_values,  locations,  params['original_page_load']))
                conn.commit()
                result = dbc.fetchone()[0]
                broadcast_message = showErr(int(result))
        except GeneralBusinessErr,e:
                broadcast_message = e.getErrMsg()
        except Exception,e: 
                errmsg = "%s" % e.__str__ + ' : ' + ','.join([str(arg) for arg in e.args])
                broadcast_message = errmsg.replace('<','').replace('>','').replace('.__str__','')
        except:
                broadcast_message = 'An undefined error occurred'
        finally:
                util.redirect(req,"/bin/crm.py?broadcast_message=%s"%broadcast_message)

def test(req,**params):
	session = Session.Session(req,timeout=info.session_timeout)
	info.setSessionTimeout(session)
	s=''
	s += '------------session-------------\n'
	s += '%-30s:%s\n' % ('id',session.id() )
	s += '%-30s:%s\n' % ('create_time',session.created() )
	s += '%-30s:%s\n' % ('last_accessed',session.last_accessed() )
	s += '%-30s:%s\n' % ('time_left',session.last_accessed() - session.created() )
	return s
